-- @owner: wang_zhengyuan
-- @date: 2022/02/23
-- @testpoint:  查询的目标列使用伪列connect by,查询中表和子查询做 nest loop 连接,支持并行查询
--step1:建表并插入数据;expect:成功
drop table if exists t_join_parallel_101 cascade ;
 
drop table if exists t_join_parallel_102 cascade ;
 
create table t_join_parallel_101(id int,c_int int not null,c_vchar varchar(100) not null,
    c_clob clob not null,c_blob blob not null,c_date date) with (storage_type=ustore);
 
create table t_join_parallel_102(id int,c_int int not null,c_vchar varchar(100) not null,
    c_clob clob not null,c_blob blob not null,c_date date) with (storage_type=ustore);
 
insert into t_join_parallel_101 values(1,1000,'abc123',lpad('123abc',50,'abc'),lpad('11100011',50,'1100')::blob,
    to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
 
insert into t_join_parallel_102 values(1,1000,'abc123',lpad('123abc',50,'abc'),lpad('11100011',50,'1100')::blob,
    to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
 
--step2:创建存储过程并调用;expect:成功
create or replace procedure proc_insert(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
                sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_vchar||'||i||',c_clob||'||i||',c_blob'||',c_date from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/
 
call proc_insert('t_join_parallel_101',10,60);
call proc_insert('t_join_parallel_102',5,90);

--step3:创建索引及视图;expect:创建成功
create index idx_join_parallel_101_1 on t_join_parallel_101(c_int);
 
create index idx_join_parallel_101_2 on t_join_parallel_101(c_int,c_vchar);
 
create index idx_join_parallel_101_3 on t_join_parallel_101(c_int,c_vchar,c_date);
 
create index idx_join_parallel_102_1 on t_join_parallel_102(c_int);
 
create index idx_join_parallel_102_2 on t_join_parallel_102(c_int,c_vchar);
 
create index idx_join_parallel_102_3 on t_join_parallel_102(c_int,c_vchar,c_date);
 
create view v_t_join_parallel_101 as select * from t_join_parallel_101 where id<90;
 
create view v_t_join_parallel_102 as select * from t_join_parallel_102 where id<9;
 
--step4:查询的目标列使用伪列connect by,查询中表和子查询做 nest loop 连接,支持并行查询;expect:查询成功
explain plan for select id,c_int+1 from (select /*+parallel(4)*/ t101.id,t102.c_int from t_join_parallel_101 t101,
    (select * from t_join_parallel_102 where id<9) t102 where t101.id<5  ) order by id,c_int;
 
select id,c_int+1 from (select /*+parallel(4)*/ t101.id,t102.c_int from t_join_parallel_101 t101,
    (select * from t_join_parallel_102 where id<9) t102 where t101.id<5  ) order by id,c_int;
select id,c_int+1 from (select t101.id,t102.c_int from t_join_parallel_101 t101,
    (select * from t_join_parallel_102 where id<9) t102 where t101.id<5 ) order by id,c_int;

--step5:清理环境;expect:成功
drop index if exists idx_join_parallel_101_1;
 
drop index if exists idx_join_parallel_101_2;
 
drop index if exists idx_join_parallel_101_3;
 
drop index if exists idx_join_parallel_102_1;
 
drop index if exists idx_join_parallel_102_2;
 
drop index if exists idx_join_parallel_102_3;
 
drop view if exists v_t_join_parallel_101;
 
drop view if exists v_t_join_parallel_102;
 
drop procedure proc_insert;
 
drop table if exists t_join_parallel_101 cascade ;
 
drop table if exists t_join_parallel_102 cascade ;
 
